package cn.edu.hhu.teacher.dao;

import cn.edu.hhu.teacher.bean.Attendance;
import cn.edu.hhu.util.JDBCUtils;
import org.junit.Test;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class AttendanceDao {
    private final JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    public List<Attendance> queryAttendance(long cid, String tnum, String tname, String sid, String sname) {
        String sql = "select *  from view_all_attendance" +
                " where cid = ? and tnum like ? and tname like ? and sid like ? and sname like ?" +
                " order by tnum desc, sid asc, sign_in_time desc";
        try {
            return template.query(sql, new BeanPropertyRowMapper<>(Attendance.class), cid, tnum, tname, sid, sname);
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Test
    public void test() {
        String sql = "select *  from view_all_attendance" +
                " where cid = ? and tnum like ? and tname like ? and sid like ? and sname like ?" +
                " order by tnum desc, sid asc, sign_in_time desc";
        for (Attendance info : template.query(sql, new BeanPropertyRowMapper<>(Attendance.class), 2, "%", "%", "%", "%")) {
            System.out.println(info.getTnum() + " " + info.getTname() + " " + info.getSid() + " " + info.getSname() + " " + info.getSign_in_time());
        }
    }
}
